package com.gitee.sidihuo.platform.dbop;

import com.alibaba.fastjson.JSONObject;
import com.gitee.sidihuo.platform.utils.RedisFacade;
import com.gitee.sidihuo.utils.util.IdGenerator;
import com.gitee.sidihuo.utils.util.JasyptUtil;
import com.gitee.sidihuo.utils.util.TimeFormatter;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.session.SqlSession;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.SqlSessionUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.TimeUnit;

@Service
@Slf4j
public class DbOpExecutor {

    @Autowired(required = false)
    private SqlSessionTemplate sqlSessionTemplate;
    @Autowired(required = false)
    private RedisFacade redisFacade;

    private static String KEY_REDIS = "DbOpExecutor534_";

    public SqlSession getSqlSession() {
        return SqlSessionUtils.getSqlSession(sqlSessionTemplate.getSqlSessionFactory(),
                sqlSessionTemplate.getExecutorType(), sqlSessionTemplate.getPersistenceExceptionTranslator());
    }

    public void closSqlSession(SqlSession sqlSession) {
        SqlSessionUtils.closeSqlSession(sqlSession, sqlSessionTemplate.getSqlSessionFactory());
    }

    protected boolean validateAuth(String userId) {
        //验证权限
        return false;
    }

    protected boolean enableDelete() {
        return true;
    }

    protected boolean enableUpdate() {
        return true;
    }

    protected boolean validateSql(DbOpExecuteModel arg) {
        String sql = arg.getFinalSql();
        String executeType = arg.getExecuteType();
        if ("select".equalsIgnoreCase(executeType)) {
            if (!sql.startsWith("select ") || !sql.contains(" where ")) {
                log.warn("startsWith select");
                return false;
            }
        } else if ("insert".equalsIgnoreCase(executeType)) {
            if (!sql.startsWith("insert ")) {
                log.warn("startsWith insert");
                return false;
            }
        } else if ("update".equalsIgnoreCase(executeType)) {
            if (!enableUpdate()) {
                log.warn("not enable update");
                return false;
            }
            if (!sql.startsWith("update ") || !sql.contains(" where ")) {
                log.warn("startsWith update");
                return false;
            }
        } else if ("delete".equalsIgnoreCase(executeType)) {
            if (!enableDelete()) {
                log.warn("not enable delete");
                return false;
            }
            if (!sql.startsWith("delete ") || !sql.contains(" where ")) {
                log.warn("startsWith delete");
                return false;
            }
        }
        //验证脚本是否合法
        return true;
    }

    public DbOpExecuteModel execute(String userId, String mi, String sec) {
        String currentDate = TimeFormatter.formatCurrentDate();
        String ming = JasyptUtil.decyptPwd(sec + currentDate, mi);
        DbOpExecuteModel arg = JSONObject.parseObject(ming, DbOpExecuteModel.class);
        boolean validateAuth = validateAuth(userId);
        if (!validateAuth) {
            arg.setResultMsg("未执行（auth）");
            return arg;
        }
        boolean validateSql = validateSql(arg);
        if (!validateSql) {
            arg.setResultMsg("未执行（illegal）");
            return arg;
        }
        long timeMillisBegin = System.currentTimeMillis();
        String finalSql = arg.getFinalSql();
        //sql必须经过二次确认才行
        String redisSql = redisFacade.getCache(KEY_REDIS + arg.getExecuteId());
        if (StringUtils.equalsIgnoreCase(redisSql, finalSql)) {
            log.info("####### 待执行sql {} -> {}", arg.getExecuteId(), redisSql);
        }

        if (StringUtils.equalsIgnoreCase(arg.getExecuteType(), "select")) {
            if (StringUtils.equalsIgnoreCase(redisSql, finalSql)) {
                select(arg);
                arg.setResultMsg("执行select了！！！！！！！！！！！");
                redisFacade.setCache(KEY_REDIS + arg.getExecuteId(), "", 10, TimeUnit.MINUTES);
            } else {
                arg.setExecuteId(IdGenerator.generatorId20WithRandom5());
                arg.setResultMsg("没执行select，重新生成了执行ID ####################################");
                redisFacade.setCache(KEY_REDIS + arg.getExecuteId(), finalSql, 10, TimeUnit.MINUTES);
            }
        } else if (StringUtils.equalsIgnoreCase(arg.getExecuteType(), "insert")) {
            if (StringUtils.equalsIgnoreCase(redisSql, finalSql)) {
                insert(arg);
                arg.setResultMsg("执行insert了！！！！！！！！！！！");
                redisFacade.setCache(KEY_REDIS + arg.getExecuteId(), "", 10, TimeUnit.MINUTES);
            } else {
                arg.setResultMsg("没执行insert，重新生成了执行ID ####################################");
                arg.setExecuteId(IdGenerator.generatorId20WithRandom5());
                redisFacade.setCache(KEY_REDIS + arg.getExecuteId(), finalSql, 10, TimeUnit.MINUTES);
            }
        } else if (StringUtils.equalsIgnoreCase(arg.getExecuteType(), "update")) {
            if (StringUtils.equalsIgnoreCase(redisSql, finalSql)) {
                update(arg);
                arg.setResultMsg("执行update了！！！！！！！！！！！");
                redisFacade.setCache(KEY_REDIS + arg.getExecuteId(), "", 10, TimeUnit.MINUTES);
            } else {
                arg.setResultMsg("没执行update，重新生成了执行ID ####################################");
                arg.setExecuteId(IdGenerator.generatorId20WithRandom5());
                redisFacade.setCache(KEY_REDIS + arg.getExecuteId(), finalSql, 10, TimeUnit.MINUTES);
            }
        } else if (StringUtils.equalsIgnoreCase(arg.getExecuteType(), "delete")) {
            if (StringUtils.equalsIgnoreCase(redisSql, finalSql)) {
                delete(arg);
                arg.setResultMsg("执行delete了！！！！！！！！！！！");
                redisFacade.setCache(KEY_REDIS + arg.getExecuteId(), "", 10, TimeUnit.MINUTES);
            } else {
                arg.setResultMsg("没执行delete，重新生成了执行ID ####################################");
                arg.setExecuteId(IdGenerator.generatorId20WithRandom5());
                redisFacade.setCache(KEY_REDIS + arg.getExecuteId(), finalSql, 10, TimeUnit.MINUTES);
            }
        } else {
            arg.setResultMsg("executeType = " + arg.getExecuteType());
        }

        long timeMillisEnd = System.currentTimeMillis();
        log.info("end executeSql sql = {} time-consuming = {}", arg.getFinalSql(), timeMillisEnd - timeMillisBegin);
        return arg;
    }

    public void select(DbOpExecuteModel arg) {
        String sql = arg.getFinalSql();
        log.info("begin select = {} ", sql);
        SqlSession sqlSession = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            sqlSession = getSqlSession();
            connection = sqlSession.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            handleQueryResult(resultSet, arg);
            arg.setFinalStatus(true);
        } catch (Throwable t) {
            arg.setFinalStatus(false);
            log.warn("begin  failed {} ", sql, t);
        } finally {
            log.info("end select = {} ", sql);
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    log.warn("select close Statement failed {} ", sql, e);
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    log.warn("select close connection failed {} ", sql, e);
                }
            }
            if (sqlSession != null) {
                closSqlSession(sqlSession);
            }
        }
    }

    public void insert(DbOpExecuteModel arg) {
        String sql = arg.getFinalSql();
        log.info("begin insert = {} ", sql);
        SqlSession sqlSession = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        boolean execute = false;
        try {
            sqlSession = getSqlSession();
            connection = sqlSession.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            execute = preparedStatement.execute();
            arg.setFinalStatus(true);
        } catch (Throwable t) {
            arg.setFinalStatus(false);
            log.warn("insert  failed {} ", sql, t);
        } finally {
            log.info("end insert = {} execute = {}", sql, execute);
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    log.warn("insert close Statement failed {} ", sql, e);
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    log.warn("insert close connection failed {} ", sql, e);
                }
            }
            if (sqlSession != null) {
                closSqlSession(sqlSession);
            }
        }
    }

    public void update(DbOpExecuteModel arg) {
        String sql = arg.getFinalSql();
        log.info("begin update = {} ", sql);
        SqlSession sqlSession = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        int update = -1;
        try {
            sqlSession = getSqlSession();
            connection = sqlSession.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            update = preparedStatement.executeUpdate();
            arg.setFinalStatus(true);
        } catch (Throwable t) {
            arg.setFinalStatus(false);
            log.warn("update  failed {} ", sql, t);
        } finally {
            log.info("end update = {} update = {}", sql, update);
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    log.warn("update close Statement failed {} ", sql, e);
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    log.warn("update close connection failed {} ", sql, e);
                }
            }
            if (sqlSession != null) {
                closSqlSession(sqlSession);
            }
        }
    }

    public void delete(DbOpExecuteModel arg) {
        String sql = arg.getFinalSql();
        log.info("begin delete = {} ", sql);
        SqlSession sqlSession = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        boolean execute = false;
        try {
            sqlSession = getSqlSession();
            connection = sqlSession.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            execute = preparedStatement.execute();
            arg.setFinalStatus(true);
        } catch (Throwable t) {
            arg.setFinalStatus(false);
            log.warn("delete  failed {} ", sql, t);
        } finally {
            log.info("end delete = {} execute = {}", sql, execute);
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    log.warn("delete close Statement failed {} ", sql, e);
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    log.warn("delete close connection failed {} ", sql, e);
                }
            }
            if (sqlSession != null) {
                closSqlSession(sqlSession);
            }
        }
    }

    protected void handleQueryResult(ResultSet result, DbOpExecuteModel arg) throws SQLException {
        ResultSetMetaData rsm = result.getMetaData();
        int columns = rsm.getColumnCount();
        List<String> header = new ArrayList<>(columns);
        Map<String, String> columnTypeClassNames = new HashMap<>();
        for (int i = 1; i <= columns; i++) {
            String columnName = rsm.getColumnName(i);
            header.add(columnName);
            columnTypeClassNames.put(columnName, rsm.getColumnClassName(i));
        }
        List<DbOpRowModel> rowDtos = new ArrayList<>();
        long rowIndex = 0;
        while (result.next()) {
            rowIndex++;
            DbOpRowModel rowDto = new DbOpRowModel();
            rowDto.setRowIndex(rowIndex);
            List<DbOpColumnModel> columnDtos = new ArrayList<>(columns);
            for (String columnName : header) {
                String columnValue = result.getString(columnName);
                DbOpColumnModel columnDto = new DbOpColumnModel(columnName, columnValue);
                columnDtos.add(columnDto);
            }
            rowDto.setColumns(columnDtos);
            rowDtos.add(rowDto);
        }
        arg.setExecuteResultValues(rowDtos);
        arg.setExecuteResultRowCount(rowDtos.size());
    }

}
